﻿using System;
using System.Web;
using System.Web.Hosting;
using System.Web.Security;
using System.Web.Configuration;
using System.Security.Principal;
using System.Security.Permissions;
using System.Globalization;
using System.Runtime.Serialization;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Security.Cryptography;
using System.Text;
using System.Text.RegularExpressions;
using System.Configuration.Provider;
using System.Configuration;
using System.Web.DataAccess;
using System.Web.Management;
using System.Web.Util;
using MySql.Data.MySqlClient;

namespace Reviewer.Security
{
    public class MembershipProvider : System.Web.Security.MembershipProvider
    {
        private const int SALT_SIZE_IN_BYTES = 16;

        private static string GenerateSalt()
        {
            byte[] buf = new byte[SALT_SIZE_IN_BYTES];
            (new RNGCryptoServiceProvider()).GetBytes(buf);
            return Convert.ToBase64String(buf);
        }

        private static string GetExceptionText(int status)
        {
            string key;
            switch (status)
            {
                case 0:
                    return String.Empty;
                case 1:
                    key = "User not found";
                    break;
                case 2:
                    key = "Wrong password";
                    break;
                case 3:
                    key = "Wrong answer";
                    break;
                case 4:
                    key = "Invalid password";
                    break;
                case 5:
                    key = "Invalid question";
                    break;
                case 6:
                    key = "Invalid answer";
                    break;
                case 7:
                    key = "Invalid email";
                    break;
                default:
                    key = "Unknown provider error";
                    break;
            }
            return key;
        }

        private string connectionString;
        private string hashAlgorithmType;

        private bool enablePasswordRetrieval;
        private bool enablePasswordReset;
        private bool requiresQuestionAndAnswer;
        private bool requiresUniqueEmail;
        private MembershipPasswordFormat passwordFormat;
        private int maxInvalidPasswordAttempts;
        private int passwordAttemptWindow;
        private int minRequiredPasswordLength;
        private int minRequiredNonAlphanumericCharacters;
        private string passwordStrengthRegularExpression;
        #region properties
        public override bool EnablePasswordRetrieval { get { return this.enablePasswordRetrieval; } }
        public override bool EnablePasswordReset { get { return this.enablePasswordReset; } }
        public override bool RequiresQuestionAndAnswer { get { return this.requiresQuestionAndAnswer; } }
        public override bool RequiresUniqueEmail { get { return this.requiresUniqueEmail; } }
        public override MembershipPasswordFormat PasswordFormat { get { return this.passwordFormat; } }
        public override int MaxInvalidPasswordAttempts { get { return this.maxInvalidPasswordAttempts; } }
        public override int PasswordAttemptWindow { get { return this.passwordAttemptWindow; } }
        public override int MinRequiredPasswordLength { get { return this.minRequiredPasswordLength; } }
        public override int MinRequiredNonAlphanumericCharacters { get { return this.minRequiredNonAlphanumericCharacters; } }
        public override string PasswordStrengthRegularExpression { get { return this.passwordStrengthRegularExpression; } }

        public override string ApplicationName
        {
            get { return "Reviewer"; }
            set
            {
            }
        }
        #endregion

        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
                throw new ArgumentNullException("config");

            if (String.IsNullOrEmpty(name))
                name = "ReviewerMembershipProvider";

            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", "Reviewer Membership Provider");
            }
            base.Initialize(name, config);

            this.enablePasswordRetrieval = SecUtility.GetBooleanValue(config, "enablePasswordRetrieval", false);
            this.enablePasswordReset = SecUtility.GetBooleanValue(config, "enablePasswordReset", true);
            this.requiresQuestionAndAnswer = SecUtility.GetBooleanValue(config, "requiresQuestionAndAnswer", true);
            this.requiresUniqueEmail = SecUtility.GetBooleanValue(config, "requiresUniqueEmail", true);
            this.maxInvalidPasswordAttempts = SecUtility.GetIntValue(config, "maxInvalidPasswordAttempts", 5, false, 0);
            this.passwordAttemptWindow = SecUtility.GetIntValue(config, "passwordAttemptWindow", 10, false, 0);
            this.minRequiredPasswordLength = SecUtility.GetIntValue(config, "minRequiredPasswordLength", 7, false, 128);
            this.minRequiredNonAlphanumericCharacters = SecUtility.GetIntValue(config, "minRequiredNonalphanumericCharacters", 1, true, 128);

            this.hashAlgorithmType = config["hashAlgorithmType"];
            if (String.IsNullOrEmpty(this.hashAlgorithmType))
            {
                this.hashAlgorithmType = "SHA1";
            }

            this.passwordStrengthRegularExpression = config["passwordStrengthRegularExpression"];
            if (this.PasswordStrengthRegularExpression != null)
            {
                this.passwordStrengthRegularExpression = this.PasswordStrengthRegularExpression.Trim();
                if (this.PasswordStrengthRegularExpression.Length != 0)
                {
                    try
                    {
                        Regex regex = new Regex(this.PasswordStrengthRegularExpression);
                        // code analysis fix
                        regex.Replace("test", "t");
                    }
                    catch (ArgumentException e)
                    {
                        throw new ProviderException(e.Message, e);
                    }
                }
            }
            else
            {
                this.passwordStrengthRegularExpression = string.Empty;
            }

            //this.applicationName = config["applicationName"];
            //if (string.IsNullOrEmpty(this.applicationName))
            //    this.applicationName = SecUtility.GetDefaultAppName();

            //if (this.applicationName.Length > 255)
            //{
            //    throw new ProviderException("Provider application name is too long, max length is 255.");
            //}

            string strTemp = config["passwordFormat"];
            if (strTemp == null)
                strTemp = "Hashed";

            switch (strTemp)
            {
                case "Clear":
                    this.passwordFormat = MembershipPasswordFormat.Clear;
                    break;
                case "Encrypted":
                    this.passwordFormat = MembershipPasswordFormat.Encrypted;
                    break;
                case "Hashed":
                    this.passwordFormat = MembershipPasswordFormat.Hashed;
                    break;
                default:
                    throw new ProviderException("Bad password format");
            }

            if (this.PasswordFormat == MembershipPasswordFormat.Hashed && this.EnablePasswordRetrieval)
                throw new ProviderException("Provider cannot retrieve hashed password");

            string connectName = config["connectionStringName"];
            if (connectName == null || connectName.Length < 1)
                throw new ProviderException("Connection name not specified");

            string temp = ConfigurationManager.ConnectionStrings[connectName].ConnectionString;
            if (temp == null || temp.Length < 1)
                throw new ProviderException("Connection string not found: " + connectName);
            this.connectionString = temp;

            config.Remove("connectionStringName");
            config.Remove("enablePasswordRetrieval");
            config.Remove("enablePasswordReset");
            config.Remove("requiresQuestionAndAnswer");
            config.Remove("applicationName");
            config.Remove("requiresUniqueEmail");
            config.Remove("maxInvalidPasswordAttempts");
            config.Remove("passwordAttemptWindow");
            config.Remove("passwordFormat");
            config.Remove("name");
            config.Remove("description");
            config.Remove("minRequiredPasswordLength");
            config.Remove("minRequiredNonalphanumericCharacters");
            config.Remove("passwordStrengthRegularExpression");
            config.Remove("hashAlgorithmType");

            //Ignore additional attributes
            //if (config.Count > 0)
            //{
            //    string attribUnrecognized = config.GetKey(0);
            //    if (!String.IsNullOrEmpty(attribUnrecognized))
            //        throw new ProviderException("Provider unrecognized attribute: " + attribUnrecognized);
            //}
        }

        public override MembershipUser CreateUser(string username, string password, string email, string passwordQuestion, string passwordAnswer, bool isApproved, object providerUserKey, out MembershipCreateStatus status)
        {
            if (!SecUtility.ValidateParameter(ref password, true, true, false, 0))
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            string salt = GenerateSalt();
            string pass = EncodePassword(password, (int)this.PasswordFormat, salt);
            if (pass.Length > 128)
            {
                status = MembershipCreateStatus.InvalidPassword;
                return null;
            }

            if (!SecUtility.ValidateParameter(ref username, true, true, true, 255))
            {
                status = MembershipCreateStatus.InvalidUserName;
                return null;
            }

            if (!SecUtility.ValidateParameter(ref email, RequiresUniqueEmail, RequiresUniqueEmail, false, 128))
            {
                status = MembershipCreateStatus.InvalidEmail;
                return null;
            }

            if (!SecUtility.ValidateParameter(ref passwordQuestion, RequiresQuestionAndAnswer, true, false, 255))
            {
                status = MembershipCreateStatus.InvalidQuestion;
                return null;
            }

            if (!SecUtility.ValidateParameter(ref passwordAnswer, RequiresQuestionAndAnswer, true, false, 128))
            {
                status = MembershipCreateStatus.InvalidAnswer;
                return null;
            }

            if (providerUserKey != null)
            {
                throw new ArgumentException("userId Parameter must be null for $safeprojectname$");
            }

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    object result;
                    int uid;

                    ////////////////////////////////////////////////////////////
                    // Step 1: Check if the user exists in the Users table: create if not
                    uid = MySqlConnectionHelper.GetUserID(connection, username, true, false, DateTime.Now);
                    if (uid == 0)
                    {
                        // User not created successfully!
                        status = MembershipCreateStatus.ProviderError;
                        return null;
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 2: Check if the user exists in the Membership table: Error if yes.
                    using (MySqlCommand command = new MySqlCommand(@"SELECT UserId FROM AN_Membership WHERE UserId = @UserId", connection))
                    {
                        command.Parameters.Add(new MySqlParameter("@UserId", uid));
                        result = command.ExecuteScalar();
                        if (result != null && (result is int) && ((int)result) != 0)
                        {
                            status = MembershipCreateStatus.DuplicateUserName;
                            return null;
                        }
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 3: Check if Email is duplicate
                    if (RequiresUniqueEmail)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"SELECT u.UserId FROM AN_Membership m, AN_User u WHERE m.UserId = u.UserId AND m.Email = @Email", connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@Email", email));
                            result = command.ExecuteScalar();
                            if (result != null && (result is int) && ((int)result) != 0)
                            {
                                status = MembershipCreateStatus.DuplicateEmail;
                                return null;
                            }
                        }
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 4: Create user in Membership table
                    DateTime dt = MySqlConnectionHelper.RoundToSeconds(DateTime.Now);
                    using (MySqlCommand command = new MySqlCommand(@"INSERT INTO AN_Membership " +
                                                "(UserId, Email, Password, PasswordFormat, PasswordSalt, PasswordQuestion, PasswordAnswer, IsApproved, CreateDate, LastLoginDate, LastPasswordChangedDate) " +
                                                "VALUES (@UserId, @Email, @Pass, @PasswordFormat, @PasswordSalt, @PasswordQuestion, @PasswordAnswer, @IsApproved, @CDate, @LLDate, @LPCDate)",
                                                connection))
                    {
                        int pFormat = (int)this.PasswordFormat;
                        command.Parameters.Add(new MySqlParameter("@UserId", uid));
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@Email", MySqlDbType.VarChar, email));
                        command.Parameters.Add(new MySqlParameter("@Pass", pass));
                        command.Parameters.Add(new MySqlParameter("@PasswordFormat", pFormat));
                        command.Parameters.Add(new MySqlParameter("@PasswordSalt", salt));
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@PasswordQuestion", MySqlDbType.VarChar, passwordQuestion));
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@PasswordAnswer", MySqlDbType.VarChar, passwordAnswer));
                        command.Parameters.Add(new MySqlParameter("@IsApproved", isApproved));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@CDate", dt));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LLDate", dt));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LPCDate", dt));

                        //
                        // Error inserting row
                        //

                        if (command.ExecuteNonQuery() != 1)
                        {
                            status = MembershipCreateStatus.ProviderError;
                            return null;
                        }
                    }

                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_User " +
                                                @"SET LastActivityDate = @LastActivityDate " +
                                                @"WHERE UserId = @UserId", connection))
                    {
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", dt));
                        command.Parameters.Add(new MySqlParameter("@UserId", uid));
                        command.ExecuteNonQuery();
                    }

                    status = MembershipCreateStatus.Success;
                    return new MembershipUser(this.Name, username, uid, email, passwordQuestion, null, isApproved, false, dt, dt, dt, dt, DateTime.MinValue);
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool ChangePasswordQuestionAndAnswer(string username, string password, string newPasswordQuestion, string newPasswordAnswer)
        {
            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");
            SecUtility.CheckParameter(ref password, true, true, false, 128, "password");

            SecUtility.CheckParameter(ref newPasswordQuestion, RequiresQuestionAndAnswer, true, false, 255, "newPasswordQuestion");

            SecUtility.CheckParameter(ref newPasswordAnswer, RequiresQuestionAndAnswer, true, false, 128, "newPasswordAnswer");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    int status = 0;
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);

                    ////////////////////////////////////////////////////////////
                    // Step 1: Make sure user exists
                    if (userId == 0)
                    {
                        return false;
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 2: Make sure password is correct
                    if (!CheckPassword(connection, userId, password, out status))
                    {
                        return false;
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 3: Update table
                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_Membership " + @"SET PasswordQuestion = @PasswordQuestion, PasswordAnswer = @PasswordAnswer " + @"WHERE UserId = @UserId",
                        connection))
                    {
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@PasswordQuestion", MySqlDbType.VarChar, newPasswordQuestion));
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@PasswordAnswer", MySqlDbType.VarChar, newPasswordAnswer));
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        if (command.ExecuteNonQuery() != 1)
                        {
                            return false;
                        }
                        else
                        {
                            return true;
                        }
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Naming", "CA2204:Literals should be spelled correctly", MessageId = "PasswordRetrieval")]
        public override string GetPassword(string username, string answer)
        {
            if (!EnablePasswordRetrieval)
                throw new NotSupportedException("Membership PasswordRetrieval not supported");

            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");
            SecUtility.CheckParameter(ref answer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, false, 128, "passwordAnswer");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);
                    int pwdFormat = 0;
                    int status = 0;
                    string pass = null;
                    string salt = null;
                    bool userIsApproved;

                    pass = GetPasswordWithFormat(connection, userId, answer, RequiresQuestionAndAnswer, out pwdFormat, out status, out salt, out userIsApproved);

                    if (pass == null)
                    {
                        string errText = GetExceptionText(status);

                        if (IsStatusDueToBadPassword(status))
                        {
                            throw new MembershipPasswordException(errText);
                        }
                        else
                        {
                            throw new ProviderException(errText);
                        }
                    }
                    return UnEncodePassword(pass, pwdFormat);
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool ChangePassword(string username, string oldPassword, string newPassword)
        {
            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");
            SecUtility.CheckParameter(ref oldPassword, true, true, false, 128, "oldPassword");
            SecUtility.CheckParameter(ref newPassword, true, true, false, 128, "newPassword");

            string salt = GenerateSalt();
            string pass = EncodePassword(newPassword, (int)this.PasswordFormat, salt);
            if (pass.Length > 128)
                throw new ArgumentException("Membership password too long, max is 128");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            try
            {
                try
                {
                    int status = 0;
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);

                    ////////////////////////////////////////////////////////////
                    // Step 1: Make sure user exists
                    if (userId == 0)
                    {
                        return false;
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 2: Make sure oldPassword is correct
                    if (!CheckPassword(connection, userId, oldPassword, out status))
                    {
                        return false;
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 3: Save new password
                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_Membership " +
                                                @"SET Password = @Pass, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt, LastPasswordChangedDate = @LastPasswordChangedDate " +
                                                @"WHERE UserId = @UserId",
                                               connection))
                    {
                        int pFormat = (int)this.PasswordFormat;
                        command.Parameters.Add(new MySqlParameter("@Pass", pass));
                        command.Parameters.Add(new MySqlParameter("@PasswordFormat", pFormat));
                        command.Parameters.Add(new MySqlParameter("@PasswordSalt", salt));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastPasswordChangedDate", DateTime.Now));
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        if (command.ExecuteNonQuery() != 1)
                        {
                            throw new ProviderException(GetExceptionText(100));
                        }
                    }
                    return true;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override string ResetPassword(string username, string answer)
        {
            if (!EnablePasswordReset)
                throw new NotSupportedException("Not configured to support password resets");

            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");
            SecUtility.CheckParameter(ref answer, RequiresQuestionAndAnswer, RequiresQuestionAndAnswer, false, 128, "passwordAnswer");

            string salt = GenerateSalt();
            string newPassword = GeneratePassword();
            string pass = EncodePassword(newPassword, (int)this.PasswordFormat, salt);

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            MySqlDataReader reader = null;
            try
            {
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);

                    ////////////////////////////////////////////////////////////
                    // Step 1: Make sure user exists
                    if (userId == 0)
                    {
                        throw new ProviderException(GetExceptionText(1));
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 2: Check passwordAnswer
                    if (RequiresQuestionAndAnswer)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"SELECT PasswordAnswer " +
                                                    @"FROM AN_Membership " +
                                                    @"WHERE UserId = @UserId",
                                                    connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            reader = command.ExecuteReader();
                        }

                        if (!reader.Read())
                        {
                            // No passwordAnswer!
                            throw new MembershipPasswordException(GetExceptionText(3));
                        }

                        string storedPasswordAnswer = MySqlConnectionHelper.GetNullableString(reader, 0);
                        if (storedPasswordAnswer == null || String.Compare(storedPasswordAnswer, answer, StringComparison.OrdinalIgnoreCase) != 0)
                        {
                            throw new MembershipPasswordException(GetExceptionText(3));
                        }
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 3: Save new password
                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_Membership " +
                                                @"SET Password = @Pass, PasswordFormat = @PasswordFormat, PasswordSalt = @PasswordSalt, LastPasswordChangedDate = @LastPasswordChangedDate " +
                                                @"WHERE UserId = @UserId",
                                               connection))
                    {
                        int pFormat = (int)this.PasswordFormat;
                        command.Parameters.Add(new MySqlParameter("@Pass", pass));
                        command.Parameters.Add(new MySqlParameter("@PasswordFormat", pFormat));
                        command.Parameters.Add(new MySqlParameter("@PasswordSalt", salt));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastPasswordChangedDate", DateTime.Now));
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        if (command.ExecuteNonQuery() != 1)
                        {
                            throw new ProviderException(GetExceptionText(100));
                        }
                    }

                    return newPassword;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override void UpdateUser(MembershipUser user)
        {
            if (user == null)
            {
                throw new ArgumentNullException("user");
            }

            string temp = user.UserName;
            SecUtility.CheckParameter(ref temp, true, true, false, 255, "UserName");
            temp = user.Email;
            SecUtility.CheckParameter(ref temp, RequiresUniqueEmail, RequiresUniqueEmail, false, 128, "Email");
            user.Email = temp;

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(connection, user.UserName, false);
                    object result;

                    ////////////////////////////////////////////////////////////
                    // Step 1: Make sure user exists
                    if (userId == 0)
                    {
                        throw new ProviderException(GetExceptionText(1));
                    }

                    using (MySqlCommand command = new MySqlCommand(@"SELECT UserId FROM AN_Membership WHERE UserId = @UserId", connection))
                    {
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        result = command.ExecuteScalar();
                    }

                    if (result == null || !(result is int))
                    {
                        throw new ProviderException(GetExceptionText(1));
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 2: Make sure email is unique

                    if (this.RequiresUniqueEmail)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"SELECT u.UserId " +
                                                    @"FROM AN_Membership m, AN_User u " +
                                                    @"WHERE u.UserId <> @UserId AND m.Email = @Email AND u.UserId = m.UserId",
                                                    connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.Parameters.Add(new MySqlParameter("@Email", user.Email));

                            result = command.ExecuteScalar();
                        }
                        if (result != null && (result is int))
                        {
                            int userId2 = (int)result;
                            if (userId2 != userId)
                            {
                                throw new ProviderException("Dup user id: " + userId2.ToString(CultureInfo.InvariantCulture));
                            }
                        }
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 3: Update table

                    //
                    // Start transaction
                    //

                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_Membership " +
                                                @"SET Email = @Email, Comment = @Comment, LastLoginDate = @LastLoginDate, IsApproved = @IsApproved " +
                                                @"WHERE UserId = @UserId",
                                                connection))
                    {
                        int isapp = user.IsApproved ? 1 : 0;
                        string comm = (user.Comment == null) ? String.Empty : user.Comment;

                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@Email", MySqlDbType.VarChar, user.Email));
                        command.Parameters.Add(new MySqlParameter("@Comment", comm));
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastLoginDate", user.LastLoginDate));
                        command.Parameters.Add(new MySqlParameter("@IsApproved", isapp));
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        if (command.ExecuteNonQuery() != 1)
                        {
                            throw new ProviderException(GetExceptionText(20));
                        }
                    }

                    using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_User SET LastActivityDate = @LastActivityDate WHERE UserId = @UserId", connection))
                    {
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", user.LastActivityDate));
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        command.ExecuteNonQuery();
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool ValidateUser(string username, string password)
        {
            if (!SecUtility.ValidateParameter(ref username, true, true, false, 255))
            {
                return false;
            }

            if (!SecUtility.ValidateParameter(ref password, true, true, false, 128))
            {
                return false;
            }

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);
                    bool userIsApproved = false;
                    if (CheckPassword(connection, userId, password, out userIsApproved) && userIsApproved)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"UPDATE  AN_Membership " +
                                                    @"SET     LastLoginDate = @LastLoginDate " +
                                                    @"WHERE   UserId = @UserId",
                                               connection))
                        {
                            command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastLoginDate", DateTime.Now));
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.ExecuteNonQuery();
                        }

                        using (MySqlCommand command = new MySqlCommand(@"UPDATE  AN_User " +
                                                    @"SET     LastActivityDate = @LastActivityDate " +
                                                    @"WHERE   UserId = @UserId", connection))
                        {
                            command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", DateTime.Now));
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.ExecuteNonQuery();
                        }

                        return true;
                    }
                    else
                    {
                        return false;
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool UnlockUser(string userName)
        {
            return false;
        }

        public override MembershipUser GetUser(object providerUserKey, bool userIsOnline)
        {
            return null;
        }

        public override MembershipUser GetUser(string username, bool userIsOnline)
        {
            SecUtility.CheckParameter(ref username, true, false, true, 255, "username");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            MySqlDataReader reader = null;

            try
            {
                try
                {
                    using (MySqlCommand command = new MySqlCommand(@"SELECT Email, PasswordQuestion, Comment, CreateDate, LastLoginDate, LastActivityDate, LastPasswordChangedDate, u.UserId, IsApproved " +
                                                @"FROM AN_User u, AN_Membership m " +
                                                @"WHERE u.UserName = @UserName AND u.UserId = m.UserId",
                                               connection))
                    {

                        command.Parameters.Add(new MySqlParameter("@UserName", username));

                        reader = command.ExecuteReader();
                    }

                    if (!reader.Read())
                    {
                        return null;
                    }

                    string email = MySqlConnectionHelper.GetNullableString(reader, 0);
                    string passwordQuestion = MySqlConnectionHelper.GetNullableString(reader, 1);
                    string comment = MySqlConnectionHelper.GetNullableString(reader, 2);
                    DateTime dtCreate = reader.GetDateTime(3);
                    DateTime dtLastLogin = reader.GetDateTime(4);
                    DateTime dtLastActivity = userIsOnline ? MySqlConnectionHelper.RoundToSeconds(DateTime.Now)
                                                           : reader.GetDateTime(5);
                    DateTime dtLastPassChange = reader.IsDBNull(6) ? DateTime.MinValue : reader.GetDateTime(6);
                    int userId = reader.GetInt32(7);
                    bool isApproved = reader.GetBoolean(8);
                    reader.Close();

                    if (userIsOnline)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"UPDATE AN_User " +
                                                    @"SET LastActivityDate = @LastActivityDate " +
                                                    @"WHERE UserId = @UserId",
                                                   connection))
                        {
                            command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", dtLastActivity));
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));

                            command.ExecuteNonQuery();
                        }
                    }

                    ////////////////////////////////////////////////////////////
                    // Step 4 : Return the result
                    return new MembershipUser(this.Name, username, userId, email, passwordQuestion, comment, isApproved, false, dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange, DateTime.MinValue);
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    //if (reader != null && !reader.IsClosed)
                    //{
                    //    reader.Close();
                    //}

                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override string GetUserNameByEmail(string email)
        {
            SecUtility.CheckParameter(ref email, false, false, false, 128, "email");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            try
            {
                MySqlCommand command = null;

                try
                {
                    MySqlDataReader reader = null;
                    string username = null;

                    if (email != null)
                    {
                        command = new MySqlCommand(@"SELECT UserName " +
                                                   @"FROM AN_User u, AN_Membership m " +
                                                   @"WHERE m.Email = @Email AND u.UserId = m.UserId",
                                                   connection);
                        command.Parameters.Add(MySqlConnectionHelper.CreateMySqlParam("@Email", MySqlDbType.VarChar, email));
                    }
                    else
                    {
                        command = new MySqlCommand(@"SELECT UserName " +
                                                   @"FROM AN_User u, AN_Membership m " +
                                                   @"WHERE m.Email IS NULL AND u.UserId = m.UserId",
                                                   connection);
                    }

                    try
                    {
                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                        if (reader.Read())
                        {
                            username = MySqlConnectionHelper.GetNullableString(reader, 0);
                            if (RequiresUniqueEmail && reader.Read())
                            {
                                throw new ProviderException("Cannot have more than one user with the same email");
                            }
                        }
                        return username;
                    }
                    catch
                    {
                        throw;
                    }
                    finally
                    {
                        if (reader != null)
                            reader.Close();
                    }
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (command != null)
                        command.Dispose();

                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override bool DeleteUser(string username, bool deleteAllRelatedData)
        {
            SecUtility.CheckParameter(ref username, true, true, true, 255, "username");

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;

            try
            {
                try
                {
                    int userId = MySqlConnectionHelper.GetUserID(connection, username, false);

                    if (userId == 0)
                        return false; // User not found

                    bool returnValue = false;

                    using (MySqlCommand command = new MySqlCommand(@"DELETE FROM AN_Membership WHERE UserId = @UserId", connection))
                    {
                        command.Parameters.Add(new MySqlParameter("@UserId", userId));

                        returnValue = (command.ExecuteNonQuery() == 1);
                    }

                    if (deleteAllRelatedData)
                    {
                        using (MySqlCommand command = new MySqlCommand(@"DELETE FROM AN_UserInRole WHERE UserId = @UserId", connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.ExecuteNonQuery();
                        }

                        using (MySqlCommand command = new MySqlCommand(@"DELETE FROM AN_Profile WHERE UserId = @UserId", connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.ExecuteNonQuery();
                        }

                        using (MySqlCommand command = new MySqlCommand(@"DELETE FROM AN_PagePersonalizationPerUser WHERE UserId = @UserId", connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            command.ExecuteNonQuery();
                        }

                        using (MySqlCommand command = new MySqlCommand(@"DELETE FROM AN_User WHERE UserId = @UserId", connection))
                        {
                            command.Parameters.Add(new MySqlParameter("@UserId", userId));
                            returnValue = (command.ExecuteNonQuery() == 1);
                        }
                    }

                    return returnValue;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override MembershipUserCollection GetAllUsers(int pageIndex, int pageSize, out int totalRecords)
        {
            if (pageIndex < 0)
                throw new ArgumentException("PageIndex cannot be negative");
            if (pageSize < 1)
                throw new ArgumentException("PageSize must be positive");

            long lBound = (long)pageIndex * pageSize;
            long uBound = lBound + pageSize - 1;

            if (uBound > System.Int32.MaxValue)
            {
                throw new ArgumentException("PageIndex too big");
            }

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            MySqlDataReader reader = null;
            long recordCount = 0;
            try
            {
                MySqlCommand command = null;

                try
                {
                    MembershipUserCollection users = new MembershipUserCollection();

                    command = new MySqlCommand(@"SELECT UserName, Email, PasswordQuestion, Comment, CreateDate, LastLoginDate, LastActivityDate, LastPasswordChangedDate, IsApproved, u.UserId " +
                                               @"FROM  AN_Membership m, AN_User u " +
                                               @"WHERE  u.UserId = m.UserId " +
                                               @"ORDER BY UserName",
                                  connection);

                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);

                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount - 1 < lBound || recordCount - 1 > uBound)
                            continue;
                        string username, email, passwordQuestion, comment;
                        DateTime dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange;
                        bool isApproved;
                        int userId;
                        username = MySqlConnectionHelper.GetNullableString(reader, 0);
                        email = MySqlConnectionHelper.GetNullableString(reader, 1);
                        passwordQuestion = MySqlConnectionHelper.GetNullableString(reader, 2);
                        comment = MySqlConnectionHelper.GetNullableString(reader, 3);
                        dtCreate = reader.GetDateTime(4);
                        dtLastLogin = reader.GetDateTime(5);
                        dtLastActivity = reader.GetDateTime(6);
                        dtLastPassChange = reader.GetDateTime(7);
                        isApproved = reader.GetBoolean(8);
                        userId = reader.GetInt32(9);
                        users.Add(new MembershipUser(this.Name,
                                                      username,
                                                      userId,
                                                      email,
                                                      passwordQuestion,
                                                      comment,
                                                      isApproved,
                                                      false,
                                                      dtCreate,
                                                      dtLastLogin,
                                                      dtLastActivity,
                                                      dtLastPassChange,
                                                      DateTime.MinValue));
                    }
                    totalRecords = (int)recordCount;
                    return users;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (command != null)
                        command.Dispose();

                    if (reader != null)
                        reader.Close();
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override int GetNumberOfUsersOnline()
        {
            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            try
            {
                try
                {
                    TimeSpan ts = new TimeSpan(0, Membership.UserIsOnlineTimeWindow, 0);
                    DateTime dt = DateTime.Now.Subtract(ts);
                    object result;

                    using (MySqlCommand command = new MySqlCommand(@"SELECT COUNT(*) FROM AN_User u, AN_Membership m WHERE u.UserId = m.UserId AND LastActivityDate > @LastActivityDate",
                                               connection))
                    {
                        command.Parameters.Add(MySqlConnectionHelper.CreateDateTimeMySqlParameter("@LastActivityDate", dt));
                        result = command.ExecuteScalar();
                    }

                    if (result != null && (result is int))
                        return (int)result;
                    else
                        return 0;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    holder.Close();
                }
            }
            catch
            {
                throw;
            }
        }

        public override MembershipUserCollection FindUsersByName(string usernameToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SecUtility.CheckParameter(ref usernameToMatch, true, true, false, 255, "usernameToMatch");

            if (pageIndex < 0)
                throw new ArgumentException("PageIndex cannot be negative");
            if (pageSize < 1)
                throw new ArgumentException("PageSize must be positive");

            long lBound = (long)pageIndex * pageSize;
            long uBound = lBound + pageSize - 1;

            if (uBound > System.Int32.MaxValue)
            {
                throw new ArgumentException("PageIndex too big");
            }

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            MySqlDataReader reader = null;
            long recordCount = 0;
            try
            {
                try
                {
                    MembershipUserCollection users = new MembershipUserCollection();

                    using (MySqlCommand command = new MySqlCommand(@"SELECT UserName, Email, PasswordQuestion, Comment, CreateDate, LastLoginDate, LastActivityDate, LastPasswordChangedDate, IsApproved, u.UserId " +
                                                @"FROM  AN_Membership m, AN_User u " +
                                                @"WHERE u.UserId = m.UserId AND " +
                                                @"UserName like @UserName " +
                                                @"ORDER BY UserName", connection))
                    {
                        command.Parameters.Add(new MySqlParameter("@UserName", usernameToMatch));
                        reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    }
                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount - 1 < lBound || recordCount - 1 > uBound)
                            continue;

                        string username, email, passwordQuestion, comment;
                        DateTime dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange;
                        bool isApproved;
                        int userId;

                        username = MySqlConnectionHelper.GetNullableString(reader, 0);
                        email = MySqlConnectionHelper.GetNullableString(reader, 1);
                        passwordQuestion = MySqlConnectionHelper.GetNullableString(reader, 2);
                        comment = MySqlConnectionHelper.GetNullableString(reader, 3);
                        dtCreate = reader.GetDateTime(4);
                        dtLastLogin = reader.GetDateTime(5);
                        dtLastActivity = reader.GetDateTime(6);
                        dtLastPassChange = reader.GetDateTime(7);
                        isApproved = reader.GetBoolean(8);
                        userId = reader.GetInt32(9);
                        users.Add(new MembershipUser(this.Name,
                                                      username,
                                                      userId,
                                                      email,
                                                      passwordQuestion,
                                                      comment,
                                                      isApproved,
                                                      false,
                                                      dtCreate,
                                                      dtLastLogin,
                                                      dtLastActivity,
                                                      dtLastPassChange,
                                                      DateTime.MinValue));
                    }
                    return users;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (reader != null)
                        reader.Close();

                    holder.Close();
                    totalRecords = (int)recordCount;
                }
            }
            catch
            {
                throw;
            }
        }

        public override MembershipUserCollection FindUsersByEmail(string emailToMatch, int pageIndex, int pageSize, out int totalRecords)
        {
            SecUtility.CheckParameter(ref emailToMatch, false, false, false, 128, "emailToMatch");

            if (pageIndex < 0)
                throw new ArgumentException("PageIndex cannot be negative");
            if (pageSize < 1)
                throw new ArgumentException("PageSize must be positive");

            long lBound = (long)pageIndex * pageSize;
            long uBound = lBound + pageSize - 1;

            if (uBound > System.Int32.MaxValue)
            {
                throw new ArgumentException("PageIndex too big");
            }

            MySqlConnectionHolder holder = MySqlConnectionHelper.GetConnection(this.connectionString);
            MySqlConnection connection = holder.connectionInstance;
            MySqlDataReader reader = null;
            long recordCount = 0;
            try
            {
                MySqlCommand command = null;

                try
                {
                    MembershipUserCollection users = new MembershipUserCollection();

                    if (emailToMatch != null)
                    {
                        command = new MySqlCommand(@"SELECT UserName, Email, PasswordQuestion, Comment, CreateDate, LastLoginDate, LastActivityDate, LastPasswordChangedDate, IsApproved, u.UserId "
                                                + @"FROM  AN_Membership m, AN_User u " +
                                                @"WHERE u.UserId = m.UserId AND Email like @Email " + @"ORDER BY UserName", connection);
                        command.Parameters.Add(new MySqlParameter("@Email", emailToMatch));
                    }
                    else
                    {
                        command = new MySqlCommand(@"SELECT UserName, Email, PasswordQuestion, Comment, CreateDate, LastLoginDate, LastActivityDate, LastPasswordChangedDate, IsApproved, u.UserId "
                                                + @"FROM AN_Membership m, AN_User u " +
                                                @"WHERE u.UserId = m.UserId AND Email IS NULL " + @"ORDER BY UserName", connection);
                    }
                    reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
                    while (reader.Read())
                    {
                        recordCount++;
                        if (recordCount - 1 < lBound || recordCount - 1 > uBound)
                            continue;

                        string username, email, passwordQuestion, comment;
                        DateTime dtCreate, dtLastLogin, dtLastActivity, dtLastPassChange;
                        bool isApproved;
                        int userId;

                        username = MySqlConnectionHelper.GetNullableString(reader, 0);
                        email = MySqlConnectionHelper.GetNullableString(reader, 1);
                        passwordQuestion = MySqlConnectionHelper.GetNullableString(reader, 2);
                        comment = MySqlConnectionHelper.GetNullableString(reader, 3);
                        dtCreate = reader.GetDateTime(4);
                        dtLastLogin = reader.GetDateTime(5);
                        dtLastActivity = reader.GetDateTime(6);
                        dtLastPassChange = reader.GetDateTime(7);
                        isApproved = reader.GetBoolean(8);
                        userId = reader.GetInt32(9);
                        users.Add(new MembershipUser(this.Name,
                                                      username,
                                                      userId,
                                                      email,
                                                      passwordQuestion,
                                                      comment,
                                                      isApproved,
                                                      false,
                                                      dtCreate,
                                                      dtLastLogin,
                                                      dtLastActivity,
                                                      dtLastPassChange,
                                                      DateTime.MinValue));
                    }
                    return users;
                }
                catch (Exception e)
                {
                    throw MySqlConnectionHelper.GetBetterException(e, holder);
                }
                finally
                {
                    if (command != null)
                        command.Dispose();

                    if (reader != null)
                        reader.Close();

                    holder.Close();
                    totalRecords = (int)recordCount;
                }
            }
            catch
            {
                throw;
            }
        }

        private string EncodePassword(string pass, int passwordFormatId, string salt)
        {
            if (passwordFormatId == 0) // MembershipPasswordFormat.Clear
                return pass;

            byte[] bIn = Encoding.Unicode.GetBytes(pass);
            byte[] bSalt = Convert.FromBase64String(salt);
            byte[] bAll = new byte[bSalt.Length + bIn.Length];
            byte[] bRet = null;

            Buffer.BlockCopy(bSalt, 0, bAll, 0, bSalt.Length);
            Buffer.BlockCopy(bIn, 0, bAll, bSalt.Length, bIn.Length);
            if (passwordFormatId == 1)
            {
                // MembershipPasswordFormat.Hashed
                using (HashAlgorithm s = HashAlgorithm.Create(this.hashAlgorithmType))
                {

                    // If the hash algorithm is null (and came from config), throw a config exception
                    if (s == null)
                    {
                        throw new ProviderException("Could not create a hash algorithm");
                    }
                    bRet = s.ComputeHash(bAll);
                }
            }
            else
            {
                bRet = EncryptPassword(bAll);
            }

            return Convert.ToBase64String(bRet);
        }

        private string UnEncodePassword(string pass, int passwordFormatId)
        {
            switch (passwordFormatId)
            {
                case 0: // MembershipPasswordFormat.Clear:
                    return pass;
                case 1: // MembershipPasswordFormat.Hashed:
                    throw new ProviderException("Provider can not decode hashed password");
                default:
                    byte[] bIn = Convert.FromBase64String(pass);
                    byte[] bRet = DecryptPassword(bIn);
                    if (bRet == null)
                        return null;
                    return Encoding.Unicode.GetString(bRet, SALT_SIZE_IN_BYTES, bRet.Length - SALT_SIZE_IN_BYTES);
            }
        }

        private bool CheckPassword(MySqlConnection connection, int userId, string password, out int status)
        {
            string salt;
            bool userIsApproved;
            int pwdFormat;
            string pass = GetPasswordWithFormat(connection, userId, null, false, out pwdFormat, out status, out salt, out userIsApproved);
            string pass2 = EncodePassword(password, pwdFormat, salt);
            return (pass == pass2);
        }

        private bool CheckPassword(MySqlConnection connection, int userId, string password, out bool userIsApproved)
        {
            string salt;
            int pwdFormat, status;
            string pass = GetPasswordWithFormat(connection, userId, null, false, out pwdFormat, out status, out salt, out userIsApproved);
            string pass2 = EncodePassword(password, pwdFormat, salt);
            return (pass == pass2);
        }

        private static string GetPasswordWithFormat(MySqlConnection connection, int userId, string passwordAnswer, bool questionAndAnswer, out int passwordFormatId, out int status, out string passwordSalt, out bool userIsApproved)
        {
            MySqlDataReader reader;
            string storedPasswordAnswer;
            string pass;

            passwordFormatId = 0;
            status = 1; // status = user not found
            passwordSalt = String.Empty;
            userIsApproved = false;
            if (userId == 0)
                return null;

            using (MySqlCommand command = new MySqlCommand(@"SELECT PasswordFormat, Password, PasswordAnswer, PasswordSalt, IsApproved " +
                                        @"FROM AN_Membership m, AN_User u " +
                                        @"WHERE m.UserId = @UserId AND m.UserId = u.UserId",
                                       connection))
            {
                command.Parameters.Add(new MySqlParameter("@UserId", userId));

                reader = command.ExecuteReader();
            }

            if (!reader.Read())
            {
                // Zero rows read = user-not-found
                reader.Close();
                return null;
            }

            passwordFormatId = reader.GetInt32(0);
            pass = MySqlConnectionHelper.GetNullableString(reader, 1);
            storedPasswordAnswer = MySqlConnectionHelper.GetNullableString(reader, 2);
            passwordSalt = MySqlConnectionHelper.GetNullableString(reader, 3);
            userIsApproved = reader.GetBoolean(4);

            if (questionAndAnswer && String.Compare(storedPasswordAnswer, passwordAnswer, StringComparison.OrdinalIgnoreCase) != 0)
            {
                status = 3;
                pass = null;
            }
            else
            {
                status = 0;
            }
            reader.Close();
            return pass;
        }

        private static bool IsStatusDueToBadPassword(int status)
        {
            return (status >= 2 && status <= 6);
        }

        private const int PASSWORD_SIZE = 14;
        public virtual string GeneratePassword()
        {
            return Membership.GeneratePassword(
                      MinRequiredPasswordLength < PASSWORD_SIZE ? PASSWORD_SIZE : MinRequiredPasswordLength,
                      MinRequiredNonAlphanumericCharacters);
        }
    }
}